package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.Student;

public class StudentDao {

	public void insert(Student stu) {
		Connection con = DBUtil.getConnection();
		String sql = "insert into students values(?,?,?)";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setInt(1, stu.getId());
			pstm.setString(2, stu.getName());
			pstm.setString(3, stu.getSex());
			pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Student getOne(Integer id) {
		Student s = new Student();
		String sql = "select * from students where id=?";
		Connection con = DBUtil.getConnection();
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setInt(1, id);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				s.setId(rs.getInt("id"));
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("sex"));
				return s;
			}
			return null;
		} catch (SQLException e) {
			return null;
		}
	}

	public Student getWhole() {
		Student s = new Student();
		String sql = "select * from students";
		Connection con = DBUtil.getConnection();
		try {
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while (rs.next()) {
				s.setId(rs.getInt("id"));
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("sex"));
				return s;
			}
			;
			return null;
		} catch (SQLException e) {
			return null;
		}

	}

	public void update(Student stu) {
		Connection con = DBUtil.getConnection();
		String sql = "update students set name=?,sex=? where id=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, stu.getName());
			pstm.setString(2, stu.getSex());
			pstm.setInt(3, stu.getId());
			pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void delete(Student stu) {
		Connection con = DBUtil.getConnection();
		String sql = "delete from students  where id=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setInt(1, stu.getId());
			pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<Student> getAll() {
		List<Student> stus = new ArrayList<>();
		String sql = "select * from students";
		Connection con = DBUtil.getConnection();
		try {
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while (rs.next()) {
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("sex"));
				stus.add(s);
			}
			return stus;
		} catch (SQLException e) {
			return null;
		}
	}
}
